unit sync;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, shellapi, Forms,ZDataset,filectrl,
  Dialogs, StdCtrls,ZConnection, ExtCtrls, DB, ZAbstractTable, ZAbstractRODataset, ZAbstractDataset, Setting;

type
    SyncData = Class(TObject)  // This is an actual class definition :
    // Internal class field definitions - only accessible in this unit
    private
    // Fields and methods only accessible by this class and descendants
    protected
    // Externally accessible fields and methods
    public
      constructor Create(namafile:String; kode_login,username_login:string); overload;
      procedure writeDate(direct:string);
      function checkmember(candidate:string;list:tstringlist):boolean;
      function gettps(input:string;listin, listout,listasal:tstringlist; desap:string):string;
      function FileLastModified(Filename: string): TDateTime;
      function readate:boolean;
      procedure konek;
      procedure writeDump;
      procedure restoreDump(filename: string);
    published
      // Note that properties must use different names to local defs

  end;                    // End of the TFruit class definition

var
  locFile,lastDate:String;
  kode,username:string;
  Zcon:TZConnection;
  zquery, zquery2:tzquery;

implementation

constructor SyncData.Create(namafile:String; kode_login,username_login:string);
var
  setting:TSetting_Form;
begin
  if(username='')then username:='admin';
  if(kode='')then kode:='00';
  setting:=TSetting_Form.create;
  zcon:=TZConnection.Create(application);
  Zcon.HostName:=setting.host;
  Zcon.Protocol:=setting.protocol;
  Zcon.Database:=setting.database;
  Zcon.User:=setting.user;
  Zcon.Password := setting.password;
  Zcon.LoginPrompt := False;
  Zcon.Connected := True;
  zquery:=TZQuery.Create(application);
  zquery.Connection:=Zcon;
  zquery2:=TZQuery.Create(application);
  zquery2.Connection:=Zcon;

  locFile:=namafile;
  kode:=kode_login;
  username:=username_login;
end;

function SyncData.FileLastModified(FileName: string): TDateTime;
var 
  intFileAge: LongInt;
begin
  intFileAge := FileAge(FileName);
  if intFileAge = -1 then
    Result := 0
  else
    Result := FileDateToDateTime(intFileAge)
end;

procedure SyncData.konek;
var
  setting:TSetting_Form;
begin
  Zcon.Free;
  ZQuery.Free;
  ZQuery2.Free;
  setting:=TSetting_Form.create;
  zcon:=TZConnection.Create(application);
  Zcon.HostName:=setting.host;
  Zcon.Protocol:=setting.protocol;
  Zcon.Database:=setting.database;
  Zcon.User:=setting.user;
  Zcon.Password := setting.password;
  Zcon.LoginPrompt := False;
  Zcon.Connected := True;
  zquery:=TZQuery.Create(application);
  zquery.Connection:=Zcon;
  zquery2:=TZQuery.Create(application);
  zquery2.Connection:=Zcon;
end;

procedure SyncData.writeDump;
var
  filename,dir,tanggal:string;
  sqlstr:string;
  Stringlist:tStringList;
begin
  if SelectDirectory('Select a directory', 'C:\, D:\, E:\', dir) then
  begin
    konek;
    tanggal := FormatDateTime('dd-mm-yyyy_hhmmss', Now());
    filename := dir+'/dump-'+tanggal+'.sql';
    StringList:=TStringList.Create;
    //bikin dump
    sqlstr := 'c:/mysql/bin/mysqldump -u root '+Zcon.database+' > "'+filename+'"';
    // Write all the bat contents
    StringList.Add(sqlstr);
    StringList.Add('pause');
    StringList.SaveToFile('dump.bat');
    //Run
    WinExec('dump.bat', SW_SHOW);
    //ShellExecute(0, nil, 'cmd.exe', PAnsiChar(sqlstr), nil, SW_HIDE);
    //WaitForSingleObject()
  end;
end;

procedure SyncData.restoreDump(filename:string);
var
  dir,tanggal:string;
  sqlstr:string;
  Stringlist:tStringList;
begin
    konek;
    //showmessage(zcon.database);
    //tanggal := FormatDateTime('dd-mm-yyyy_hhmmss', Now());
    //filename := dir+'/dump-'+tanggal+'.sql';
    StringList:=TStringList.Create;
    //bikin dump

    sqlstr := 'c:/mysql/bin/mysql -u root '+Zcon.database+' < "'+filename+'"';
    // Write all the bat contents
    StringList.Add(sqlstr);
    StringList.Add('pause');
    StringList.SaveToFile('restore.bat');
    //Run
    WinExec('restore.bat', SW_SHOW);
    //ShellExecute(0, nil, 'cmd.exe', PAnsiChar(sqlstr), nil, SW_HIDE);
    //WaitForSingleObject()
end;

procedure SyncData.writeDate(direct:String);
var
  tanggal:String;
  tanggal_acuan:String;
  mFile:textfile;
  textrow, idtanggal,iduser:string;
  i:integer;
  dir:string;
  tanggalskrg:string;
  query_terakhir, idtanggal_terakhir, tanggal_terakhir: string;
begin
  if direct<>'' then
  begin
    dir:=direct;
      //showMessage(tanggal);
      //ShowMessage('hahay');
      //cari tanggal terakhir sinkronisasi
      query_terakhir := 'SELECT a.ID_TANGGAL AS IDT, b.TANGGAL AS TG FROM history_sinkronisasi a INNER JOIN tanggal b ON a.ID_TANGGAL = b.ID_TANGGAL ORDER BY a.ID DESC';
      with zquery do
      begin
        active:=false;
        sql.clear;
        sql.Text:=query_terakhir;
        active:=true;
        first;
        if(recordcount > 0) then begin //udah pernah sinkronisasi
          idtanggal_terakhir:=fieldByName('IDT').asstring;
          tanggal_terakhir:=FormatDateTime('yyyy-mm-dd hh:mm:ss',fieldByName('TG').AsDateTime);
        end
        else begin
          idtanggal_terakhir:= '1';
          tanggal_terakhir:= '2012-10-01 00:00:01';
        end;
      end;
      //showmessage(idtanggal_terakhir);
      //showmessage(tanggal_terakhir);
      tanggal := FormatDateTime('yyyy-mm-dd hh:mm:ss', Now());
      //tanggalskrg := FormatDateTime('dd-mm-yyyy', Now());
      with zquery do
      begin
        Active:=False;
        sql.Clear;
        sql.Text := 'INSERT INTO tanggal (TANGGAL) VALUES ("'+tanggal+'")';
        ExecSQL;
      //ShowMessage('hahay1');
        active:=false;
        sql.clear;
        sql.text:='select * from tanggal order by ID_TANGGAL desc';
      //ShowMessage('hahay2');
        active:=true;
        first;
        idtanggal:=fieldbyname('ID_TANGGAL').asstring;
        tanggal_acuan:=fieldbyname('TANGGAL').asstring;

        active:=false;
        sql.clear;
        sql.text:='select * from user where USERNAME='+quotedstr(username);
        //      ShowMessage('hahay3');
        active:=true;
        first;
        iduser:=fieldbyname('ID_USER').asstring;
      end;
      AssignFile(mfile,dir+'\'+locFile);
      if FileExists(dir+'\'+locFile) then
        Append(mFile)
      else
        Rewrite(mFile);
      writeln(mfile,'['+kode+'-'+username+']');
      writeln(mfile,'[TPS]');
      with zquery do
      begin
        active:=false;
        sql.clear;
        //      ShowMessage('select * from daftar_tps where TANGGAL>'+quotedstr(TANGGAL));
        sql.text:='select * from daftar_tps where TANGGAL > '+quotedstr(tanggal_terakhir);
        active:=true;
        first;
        while (not eof) do
        begin
          textrow:='';
          for i:=0 to 4 do
          begin
            if((textrow<>'')) then textrow:=textrow+'$';
            if(i <> 4) then textrow:=textrow+fields[i].asstring;
            if(i = 4) then textrow:=textrow+FormatDateTime('yyyy-mm-dd hh-mm-ss',fields[i].asDateTime);
            if(fields[i].asstring='')then textrow:=textrow+' ';
          end;

          {textrow:=fieldbyname('ID_TPS').asstring+'$'+fieldbyname('NAMA').asstring+'$'+fieldbyname('KODE_DESA').asstring+'$'+fieldbyname('ALAMAT').asstring+'$'+fieldbyname('TANGGAL').asstring;}
          writeln(mfile,textrow);
          next;
        end;

      end;
      closeFile(mFile);
      AssignFile(mfile,dir+'\'+locFile);
      if FileExists(dir+'\'+locFile) then
        Append(mFile)
      else
        Rewrite(mFile);
      writeln(mfile,'##########');
      writeln(mfile,'[HISTORY_GANDA]');
      with zquery do
      begin
        active:=false;
        sql.clear;
        sql.text:='select * from history_ganda where TANGGAL>'+quotedstr(tanggal_terakhir);
        //ShowMessage('select * from history_ganda where TANGGAL>'+quotedstr(TANGGAL));
        active:=true;
        first;
        while (not eof) do
        begin
          textrow:=fieldbyname('ID').asstring+'$'+fieldbyname('ID_USER1').asstring+'$'+fieldbyname('ID_USER2').asstring;
          writeln(mfile,textrow);
          next;
        end;

      end;
      closeFile(mFile);
      AssignFile(mfile,dir+'\'+locFile);
      if FileExists(dir+'\'+locFile) then
        Append(mFile)
      else
        Rewrite(mFile);
      writeln(mfile,'##########');
      writeln(mfile,'[HISTORY_PEMILIH]');
      with zquery do
      begin
        active:=false;
        sql.clear;
        sql.text:='select * from history where TANGGAL_AKSI > '+quotedstr(TANGGAL_terakhir);
        //showmessage('select * from history where TANGGAL_AKSI > '+quotedstr(TANGGAL_terakhir));
        active:=true;
        first;
        while (not eof) do
        begin
          textrow:='';
          for i:=1 to 32 do
          begin
            if((textrow<>'')) then textrow:=textrow+'$';
            if(i <> 30) then textrow:=textrow+fields[i].asstring;
            if(i=30) then textrow:=textrow+FormatDateTime('yyyy-mm-dd hh:mm:ss',fields[i].AsDateTime);
            if(fields[i].asstring='')then textrow:=textrow+' ';
          end;
          //showmessage(textrow);
          {textrow:=fieldbyname('ID_USER').asstring+'$'+fieldbyname('NIK').asstring+
          '$'+fieldbyname('NKK').asstring+'$'+fieldbyname('NAMA').asstring+'$'+fieldbyname('TEMPAT').asstring+
          '$'+fieldbyname('TANGGAL').asstring+'$'+fieldbyname('IBU').asstring+'$'+fieldbyname('AGAMA').asstring+
          '$'+fieldbyname('KAWIN').asstring+'$'+fieldbyname('KELAMIN').asstring+'$'+fieldbyname('KELAINAN').asstring+
          '$'+fieldbyname('ALAMAT').asstring+'$'+fieldbyname('RW').asstring+'$'+fieldbyname('RT').asstring+
          '$'+fieldbyname('KET').asstring+'$'+fieldbyname('ID_TANGGAL').asstring+'$'+fieldbyname('TPS').asstring+
          '$'+fieldbyname('STATUS').asstring+'$'+fieldbyname('GANDA').asstring+'$'+fieldbyname('TANGGAL_AKSI').asstring+
          '$'+fieldbyname('DUSUN').asstring+'$'+fieldbyname('STATUS1').asstring+'$'+fieldbyname('STATUS2').asstring+
          '$'+fieldbyname('STATUS3').asstring+'$'+fieldbyname('STATUSP').asstring+'$'+fieldbyname('ALASANP').asstring+
          '$'+fieldbyname('CACAT').asstring+'$'+fieldbyname('PENDIDIKAN').asstring+'$'+fieldbyname('PEKERJAAN').asstring+
          '$'+fieldbyname('STATUSR').asstring+'$'+fieldbyname('WN').asstring+'$'+fieldbyname('USERNAME').asstring;}
          //kalo akun bukan kabupaten
          if(length(kode) > 2) then
            writeln(mfile,textrow);
          next;
        end;
      end;
      writeln(mfile,'##########');
      writeln(mfile,'[PEMILIH]');
      with zquery do
      begin
        active:=false;
        sql.clear;
        sql.text:='SHOW TABLES LIKE '+quotedStr('pemilih_'+kode+'%');
        active:=true;
        first;
        while (not eof) do
        begin
          if(fields[0].asstring <> 'pemilih_ganda') then begin
          writeln(mfile,fields[0].asstring);
          zquery2.Active:=false;
          zquery2.sql.clear;
          zquery2.sql.text:='select * from '+fields[0].asstring+' where ID_TANGGAL>'+quotedstr(idtanggal_terakhir);
          zquery2.Active:=true;
          zquery2.first;
          while (not zquery2.eof) do
          begin
            closeFile(mFile);
            AssignFile(mfile,dir+'\'+locFile);
            if FileExists(dir+'\'+locFile) then
              System.append(mfile)
            else
              Rewrite(mFile);
            textrow:='';
            //showmessage('mulai' +fieldbyname('NAMA').asstring+' '+inttostr(zquery2.RecordCount));
            for i:=0 to 28 do
            begin
              if(textrow<>'') then textrow:=textrow+'$';
              textrow:=textrow+zquery2.fields[i].asstring;
              if(zquery2.fields[i].asstring='') then textrow:=textrow+' ';
            end;
            //showmessage(textrow);
            //textrow:=zquery2.fieldbyname('ID_USER').asstring+'$'+zquery2.fieldbyname('NIK').asstring+'$'+zquery2.fieldbyname('NKK').asstring;
            writeln(mfile,textrow);
            zquery2.Next;
          end;

          writeln(mfile,'#####');
          end;
        next;
        end;

      end;
      closeFile(mFile);
      AssignFile(mfile,dir+'\'+locFile);
      if FileExists(dir+'\'+locFile) then
        Append(mFile)
      else
        Rewrite(mFile);
      writeln(mfile,'##########');
      writeln(mfile,'[PETUGAS]');
      with zquery do
      begin
        active:=false;
        sql.clear;
        sql.text:='select * from petugas';
        //showmessage('select * from history where TANGGAL_AKSI > '+quotedstr(TANGGAL_terakhir));
        active:=true;
        first;
        while (not eof) do
        begin
          textrow:='';
          for i:=0 to 6 do
          begin
            if((textrow<>'')) then textrow:=textrow+'$';
            textrow:=textrow+fields[i].asstring;
            if(fields[i].asstring='')then textrow:=textrow+' ';
          end;
          writeln(mfile,textrow);
          next;
        end;
      end;
      writeln(mfile,'##########');
      writeln(mfile,'$$$$$$$$$$');
      CloseFile(mfile);
      with ZQuery do  //masukkin nih gan
      begin
        Active:=False;
        sql.Clear;
        sql.Text := 'INSERT INTO history_sinkronisasi (ID_TANGGAL, ID_USER) VALUES ('+quotedstr(idtanggal)+', '+quotedstr(iduser)+')';
        ExecSQL;

      end;
    end;
end;

function SyncData.checkmember(candidate:string;list:tstringlist):boolean;
var found:boolean;
  i:integer;
begin
  found:=false;
  for i:=0 to list.count-1 do
  begin
    if(list[i]=candidate)then
    begin
      found:=true;
      break;
    end;
  end;
  result:=found;
end;

function SyncData.gettps(input:string;listin, listout,listasal:tstringlist; desap:string):string;
var i:integer;
ketemu:boolean;
begin
  ketemu:=false;
  if(input <> '0') then begin
  for i:=0 to listin.count-1 do
  begin
    if(listin[i]=input)then
    begin
      if(listasal[i]=desap) then
      begin
        result:=listout[i];
        ketemu:=true;
        break;
      end;
    end;
    if (not ketemu) then
      result:=input;
  end;
  end
  else begin
    result:='0';
  end;
end;

function SyncData.readate:boolean;
var
  tanggal, temp:String;
  zFile:textfile;
  text,id_user_cari:string;
  splitter, listpenyimpan, listtpsinput,listtpsoutput, listtpsasal:tstringlist;
  kode,user, tandaakhir, tandajeda, namatabel:string;
  idmaxtps,idmaxpeta, tpsquery, tpstransferquery, desaproses, deltps:string;
  counter:integer;
  ada:boolean;
  updateque, querycek:string;
  tanggalbuat:string;
  tanggalsama:integer;
  batas, syarat:integer;
  hasil, lolos:boolean;
begin
  hasil:=true;
  tandaakhir:='$$$$$$$$$$';
  tandajeda:='##########';
  batas:=0;
  //showmessage('test1');
  if FileExists(locFile) then // check id
    begin
      //showmessage(locfile);
      tanggalbuat := FormatDateTime('yyyy-mm-dd hh:mm:ss', FileLastModified(locfile));
      //showmessage(tanggalbuat);
      AssignFile(zfile,locFile);
      //showMessage('test2');
      Reset(zFile);
      Readln(zfile, text);
      splitter:=tstringlist.create;
      ExtractStrings(['-'], [], PChar(text), splitter);
      kode:=Copy(splitter[0],2,length(splitter[0])-1);
      user:=Copy(splitter[1],1,length(splitter[1])-1);

      //cek udah pernah dimasukin belum
      with zquery do
      begin
        active:=false;
        sql.clear;
        sql.text:='select ID from log_sinkronisasi WHERE TANGGAL = '+quotedStr(tanggalbuat)+' AND USERNAME LIKE '+quotedStr(user);
        active:=true;
        first;
        tanggalsama := recordcount;
      end;

      if(tanggalsama>0) then begin
        hasil:=false;
         //showMessage('file sudah pernah dimasukkan, sinkronisasi gagal');
      end
      else
      //showmessage('cek tanggal lewat');
      begin
      while (text<>tandaakhir) do
      begin
        //showmessage('baris');
        Readln(zfile, text);
        //showmessage(text);
        if(text='[TPS]')then
        begin
          batas:=0;
          with zquery do
          begin
            active:=false;
            sql.clear;
            sql.text:='select max(ID_TPS) as maks from daftar_tps';
            active:=true;
            first;
            //showmessage(inttostr(recordcount));
            if(recordcount>0) then
              idmaxtps:=fieldbyname('maks').asstring
            else
              idmaxtps:='1';
            if(idmaxtps = '') then
              idmaxtps := '1';
            //showmessage(idmaxtps);
          end;
          with zquery do
          begin
            active:=false;
            sql.clear;
            sql.text:='select max(ID) as maks from pemetaan_tps';
            active:=true;
            first;
            //showmessage(inttostr(recordcount));
            if(recordcount > 0) then
              idmaxpeta:=inttostr(fieldbyname('maks').asInteger + 1)
            else
              idmaxpeta:='1';
            //showmessage(idmaxpeta);
            if(idmaxpeta = '') then
              idmaxpeta := '1';
          end;
          tpsquery:='';
          tpstransferquery:='';
          Readln(zfile, text);
          while(text<>tandajeda)do
          begin
            //showmessage(text);
            text:=StringReplace(text,'''',' ',[rfReplaceAll]);
            text:=StringReplace(text,'"',' ',[rfReplaceAll]);
            text:=StringReplace(text,'\',' ',[rfReplaceAll]);
            //showmessage(text);
            if(tpsquery<>'')then tpsquery:=tpsquery+', ';
            if(tpstransferquery<>'')then tpstransferquery:=tpstransferquery+', ';
            splitter:=tstringlist.Create;
            ExtractStrings(['$'], [], PChar(text), splitter);
            //showmessage(splitter[0]);
            //showmessage(inttostr(splitter.Count));
            //showmessage('test');
            idmaxtps:=inttostr(strtoint(idmaxtps)+1);
            //showmessage('idmaxtps:'+idmaxtps);
            tpsquery:=tpsquery+'('+idmaxtps+', '+quotedstr(splitter[1])+','+quotedstr(splitter[2])+','+quotedstr(splitter[3])+', '+quotedstr(splitter[4])+')';
            tpstransferquery:=tpstransferquery+'('+quotedstr(idmaxpeta)+','+quotedstr(splitter[0])+','+quotedstr(idmaxtps)+','+quotedstr(splitter[2])+')';
            idmaxpeta := inttostr(strtoint(idmaxpeta) + 1);
            Readln(zfile, text);
            batas:=batas+1;

            //delete jika sudah ada
            with zquery2 do
            begin
              active:=false;
              //showmessage(tpsquery);
              SQL.Clear;
              //showmessage('DELETE FROM daftar_tps WHERE NAMA = '+QuotedStr(splitter[1])+' AND KODE_DESA = '+QuotedStr(splitter[2]));
              SQL.Text:='DELETE FROM daftar_tps WHERE NAMA = '+QuotedStr(splitter[1])+' AND KODE_DESA = '+QuotedStr(splitter[2]);
              //showmessage(SQL.Text);
              ExecSQL;
              //showmessage(text);
            end;
            with zquery2 do
            begin
              active:=false;
              //showmessage(tpsquery);
              SQL.Clear;
              //showmessage('DELETE FROM daftar_tps WHERE NAMA = '+QuotedStr(splitter[1])+' AND KODE_DESA = '+QuotedStr(splitter[2]));
              SQL.Text:='DELETE FROM pemetaan_tps WHERE ID_ASAL = '+QuotedStr(splitter[0])+' AND ASAL = '+QuotedStr(splitter[2]);
              //showmessage(SQL.Text);
              ExecSQL;
              //showmessage(text);
            end;
            if(batas = 100) then begin
              if(tpsquery<>'')then
              begin
                with zquery2 do
                begin
                  active:=false;
                  sql.clear;
                  sql.text:='insert into daftar_tps (ID_TPS, NAMA, KODE_DESA, ALAMAT, TANGGAL) values '+tpsquery;
                  execsql;
                end;
              end;
              if(tpstransferquery<>'')then
              begin
                with zquery2 do
                begin
                  active:=false;
                  sql.clear;
                  sql.text:='insert into pemetaan_tps (ID, ID_ASAL, ID_TUJUAN, ASAL) values '+tpstransferquery;
                  execsql;
                end;
              end;
              batas:=0;
              tpsquery:='';
              tpstransferquery:='';
            end;
            splitter.Free;
            //showmessage(text);
          end;
          //showmessage('asyik');
          //showmessage(tpsquery);
          //showmessage(tpstransferquery);
          if(tpsquery<>'')then
          begin
            with zquery2 do
            begin
              active:=false;
              sql.clear;
              sql.text:='insert into daftar_tps (ID_TPS, NAMA, KODE_DESA, ALAMAT, TANGGAL) values '+tpsquery;
              execsql;
            end;
          end;
          if(tpstransferquery<>'')then
          begin
            with zquery2 do
            begin
              active:=false;
              sql.clear;
              sql.text:='insert into pemetaan_tps (ID, ID_ASAL, ID_TUJUAN, ASAL) values '+tpstransferquery;
              execsql;
            end;
          end;
          //showmessage('TPS beres');
        end
        else if(text='[HISTORY_GANDA]')then
        begin
          batas:=0;
        //showmessage('hore');
          tpsquery:='';
          Readln(zfile, text);
          while(text<>tandajeda)do
          begin
            if(tpsquery<>'')then tpsquery:=tpsquery+', ';
            splitter:=tstringlist.Create;
            ExtractStrings(['$'], [], PChar(text), splitter);
            tpsquery:=tpsquery+'('+quotedstr(idmaxtps)+', '+quotedstr(splitter[1])+','+quotedstr(splitter[2])+')';
            splitter.Free;
            Readln(zfile, text);
          end;
          if(tpsquery<>'')then
          begin
            with zquery2 do
            begin
              active:=false;
              sql.clear;
              sql.text:='insert into history_ganda (ID_USER1, ID_USER2) values '+tpsquery;
              execsql;
            end;
          end;
          //showmessage('history ganda lewat');
        end
        else if(text='[HISTORY_PEMILIH]')then
        begin
          batas:=0;        //showmessage('wuhuw');
          tpsquery:='';
          Readln(zfile, text);
          //text:=text.StrRemoveChars(sRegions, ['''']);
          text:=StringReplace(text,'"',' ',[rfReplaceAll]);
          text:=StringReplace(text,'\',' ',[rfReplaceAll]);
          text:=StringReplace(text,'''',' ',[rfReplaceAll]);
          while(text<>tandajeda)do
          begin
            splitter:=tstringlist.Create;
            ExtractStrings(['$'], [], PChar(StringReplace(text,'''',' ',[rfReplaceAll])), splitter);
            if(splitter.Count = 32) then begin
              if(tpsquery<>'')then tpsquery:=tpsquery+', ';
              //showmessage(inttostr(splitter.Count)+' --- '+splitter[3]+' --- '+text);
              tpsquery:=tpsquery+'('+quotedstr(splitter[0])+', '+quotedstr(splitter[1])+','+quotedstr(splitter[2])+','+
              quotedstr(splitter[3])+','+quotedstr(splitter[4])+','+quotedstr(splitter[5])+
              ','+quotedstr(splitter[6])+','+quotedstr(splitter[7])+','+quotedstr(splitter[8])+
              ','+quotedstr(splitter[9])+','+quotedstr(splitter[10])+','+quotedstr(splitter[11])+
              ','+quotedstr(splitter[12])+','+quotedstr(splitter[13])+','+quotedstr(splitter[14])+
              ','+quotedstr(splitter[15])+','+quotedstr(splitter[16])+','+quotedstr(splitter[17])+
              ','+quotedstr(splitter[18])+','+quotedstr(splitter[19])+','+quotedstr(splitter[20])+
              ','+quotedstr(splitter[21])+','+quotedstr(splitter[22])+','+quotedstr(splitter[23])+
              ','+quotedstr(splitter[24])+','+quotedstr(splitter[25])+','+quotedstr(splitter[26])+
              ','+quotedstr(splitter[27])+','+quotedstr(splitter[28])+','+quotedstr(splitter[29])+
              ','+quotedstr(splitter[30])+','+quotedstr(splitter[31])+')';
            end;
            splitter.Free;
            Readln(zfile, text);
            batas:=batas+1;
            if(batas = 100) then begin
              if(tpsquery<>'')then
              begin
                //konek;
                //showmessage('sampesini2');
                with zquery2 do
                begin
                  active:=false;
                  sql.clear;
                  sql.text:='insert into history (ID_USER, NIK, NKK, NAMA, TEMPAT, TANGGAL, IBU, AGAMA, KAWIN, KELAMIN, KELAINAN, ALAMAT, RW, RT, DUSUN, KET, ID_TANGGAL, ';
                  sql.text:=sql.text+'TPS, STATUS1, STATUS2, GANDA, STATUS3, STATUSP, ALASANP, CACAT, PENDIDIKAN, PEKERJAAN, STATUSR, WN, TANGGAL_AKSI, USERNAME, KET_AKSI) values '+tpsquery;
                  execsql;
                end;
                //showmessage('sampesini3');
              end;
              batas:=0;
              tpsquery:='';
            end;
          end;
          if(tpsquery<>'')then
          begin
            //konek;
            //showmessage('sampesini2');
            with zquery2 do
            begin
              active:=false;
              sql.clear;
              sql.text:='insert into history (ID_USER, NIK, NKK, NAMA, TEMPAT, TANGGAL, IBU, AGAMA, KAWIN, KELAMIN, KELAINAN, ALAMAT, RW, RT, DUSUN, KET, ID_TANGGAL, ';
              sql.text:=sql.text+'TPS, STATUS1, STATUS2, GANDA, STATUS3, STATUSP, ALASANP, CACAT, PENDIDIKAN, PEKERJAAN, STATUSR, WN, TANGGAL_AKSI, USERNAME, KET_AKSI) values '+tpsquery;
              execsql;
            end;
            //showmessage('sampesini3');
          end;
          //showmessage('history pemilih lewat');
        end
        //pemilih..
        else if(text='[PEMILIH]')then
        begin
           //showmessage(' mulai cuk');     //showmessage('wazek');
          Readln(zfile, text);
          text:=StringReplace(text,'''',' ',[rfReplaceAll, rfIgnoreCase]);
          text:=StringReplace(text,'"',' ',[rfReplaceAll]);
          text:=StringReplace(text,'\',' ',[rfReplaceAll]);
          //showmessage(text);
          listtpsoutput:=tstringlist.create;
          listtpsasal:=tstringlist.create;
          listtpsinput:=tstringlist.create;
          with zquery do
          begin
            active:=false;
            sql.clear;
            sql.text:='select * from pemetaan_tps where ASAL LIKE '+quotedstr(kode+'%');
            active:=true;
            first;
            while (not eof) do
            begin
              listtpsinput.add(fieldbyname('ID_ASAL').asstring);
              listtpsasal.add(fieldbyname('ASAL').asstring);
              listtpsoutput.add(fieldbyname('ID_TUJUAN').asstring);
              next;
            end;
          end;
          while(text<>tandajeda)do
          begin
            if(copy(text, 1,8)='pemilih_')then
            begin
              desaproses:=copy(text, 9,8);
              //showmessage('masuk '+text);
              with zquery do
              begin
                active:=false;
                sql.clear;
                sql.text:='select * from penyimpan';
                active:=true;
                first;
                listpenyimpan:=tstringlist.create;
                while (not eof)do
                begin
                  listpenyimpan.Add(fieldbyname('NAMA').asstring);
                  next;
                end;
              end;
              namatabel:=text;
              //if(checkmember(text, listpenyimpan)<>true)then
              //begin
                with ZQuery do  //masukkin nih gan
                begin
                  //pemilih
                  Active:=False;
                  sql.Clear;
                  sql.Text := 'CREATE TABLE IF NOT EXISTS '+namatabel+'('+
                          'ID_USER int(11) NOT NULL AUTO_INCREMENT,'+
                          'NIK char(36) NOT NULL,'+
                          'NKK char(36) NOT NULL,'+
                          'NAMA char(50) NOT NULL,'+
                          'TEMPAT char(36) NOT NULL,'+
                          'TANGGAL char(10) NOT NULL,'+
                          'IBU char(36) NOT NULL,'+
                          'AGAMA char(36) NOT NULL,'+
                          'KAWIN char(1) NOT NULL,'+
                          'KELAMIN char(1) NOT NULL,'+
                          'KELAINAN char(36) NOT NULL,'+
                          'ALAMAT char(150) NOT NULL,'+
                          'RW char(3) NOT NULL,'+
                          'RT char(3) NOT NULL,'+
                          'DUSUN char(30) NOT NULL,'+
                          'KET char(150) NOT NULL,'+
                          'ID_TANGGAL int(11) NOT NULL,'+
                          'TPS int(11) NOT NULL,'+
                          'STATUS1 char(1) NOT NULL,'+
                          'STATUS2 char(1) NOT NULL,'+
                          'GANDA char(1) NOT NULL,'+
                          'STATUS3 char(1) NOT NULL,'+
                          'STATUSP char(1) NOT NULL,'+
                          'ALASANP char(1) NOT NULL,'+
                          'CACAT char(1) NOT NULL,'+
                          'PENDIDIKAN char(1) NOT NULL,'+
                          'PEKERJAAN char(1) NOT NULL,'+
                          'STATUSR char(1) NOT NULL,'+
                          'WN char(1) NOT NULL,'+
                          'PRIMARY KEY (ID_USER))';
                  ExecSQL;

                  //penyimpan
                  Active:=False;
                  sql.Clear;
                  //ubah
                  sql.Text := 'INSERT IGNORE INTO penyimpan (NAMA,UPLOAD) VALUES ("'+namatabel+'","")';
                  ExecSQL;
                end;
              //end;
              tpsquery:='';
              Readln(zfile, text); // baca pemilih
              //txdshowmessage('tes '+text);
              counter:=0;
              temp:='';
              while(text<>'#####')do
              begin
                //showmessage('cuk ke-'+inttostr(counter));
                counter:=counter+1; // penghitung
                text := StringReplace(text, '"', ' ',[rfReplaceAll, rfIgnoreCase]);
                text := StringReplace(text, '\', ' ',[rfReplaceAll, rfIgnoreCase]);
                temp := temp + text;
                if(tpsquery<>'')then tpsquery:=tpsquery+', ';
                splitter:=tstringlist.Create;
                ExtractStrings(['$'], [], PChar(StringReplace(temp, '''', ' ',[rfReplaceAll, rfIgnoreCase])), splitter);
                //cari yang id nya splitter[0]
                if(splitter.Count = 29) then begin
                  id_user_cari := splitter[0];
                  //showmessage(id_user_cari);
                  //cek dulu id nya ada atau enggak
                  with zquery do
                  begin
                    active:=false;
                    sql.clear;
                    sql.text:='select * from '+namatabel+' WHERE ID_USER = '+quotedstr(id_user_cari);
                    active:=true;
                    first;
                    listpenyimpan:=tstringlist.create;
                    ada:=false;
                    while (not eof)do
                    begin
                      ada:=true;
                      listpenyimpan.Add(fieldbyname('NAMA').asstring);
                      next;
                    end;
                  end;
                  if (ada=false) then begin
                    //showmessage(splitter[6]);
                    tpsquery:=tpsquery+'('+splitter[0]+','+quotedstr(splitter[1])+','+quotedstr(splitter[2])+','+
                    quotedstr(StringReplace(splitter[3], '''', '',[rfReplaceAll, rfIgnoreCase]))+','+quotedstr(splitter[4])+','+quotedstr(splitter[5])+
                    ','+quotedstr(StringReplace(splitter[6], '''', '',[rfReplaceAll, rfIgnoreCase]))+','+quotedstr(splitter[7])+','+quotedstr(splitter[8])+
                    ','+quotedstr(splitter[9])+','+quotedstr(splitter[10])+','+quotedstr(splitter[11])+
                    ','+quotedstr(splitter[12])+','+quotedstr(splitter[13])+','+quotedstr(splitter[14])+
                    ','+quotedstr(splitter[15])+','+quotedstr(splitter[16])+','+quotedstr(gettps(splitter[17], listtpsinput,listtpsoutput,listtpsasal,desaproses))+
                    ','+quotedstr(splitter[18])+','+quotedstr(splitter[19])+','+quotedstr(splitter[20])+
                    ','+quotedstr(splitter[21])+','+quotedstr(splitter[22])+','+quotedstr(splitter[23])+
                    ','+quotedstr(splitter[24])+','+quotedstr(splitter[25])+','+quotedstr(splitter[26])+
                    ','+quotedstr(splitter[27])+','+quotedstr(splitter[28])+')';
                    //if(counter>1000)then
                    //begin
                    tpsquery:=tpsquery+';';
                    counter:=0;
                    with zquery2 do
                    begin
                      active:=false;
                      sql.clear;
                      sql.text:='insert into '+namatabel+' (ID_USER, NIK, NKK, NAMA, TEMPAT, TANGGAL, IBU, AGAMA, KAWIN, KELAMIN, KELAINAN, ALAMAT, RW, RT, DUSUN, KET, ID_TANGGAL, TPS, STATUS1, STATUS2, GANDA, STATUS3, STATUSP, ALASANP, CACAT, PENDIDIKAN, PEKERJAAN, STATUSR, WN) values '+tpsquery;
                      execsql;
                    end;
                    tpsquery:='';
                  //end
                  //else
                  //tpsquery:=tpsquery+',';
                  end
                  else begin
                  //showmessage(splitter[6]);
                    updateque := 'WHERE ID_USER = '+id_user_cari;
                    updateque := 'UPDATE '+namatabel+' SET NIK = '+
                    quotedstr(splitter[1])+', NKK = '+
                    quotedstr(splitter[2])+', NAMA = '+
                    quotedstr(StringReplace(splitter[3], '''', '',[rfReplaceAll, rfIgnoreCase]))+', TEMPAT = '+
                    quotedstr(splitter[4])+', TANGGAL = '+
                    quotedstr(splitter[5])+', IBU = '+
                    quotedstr(StringReplace(splitter[6], '''', '',[rfReplaceAll, rfIgnoreCase]))+', AGAMA = '+
                    quotedstr(splitter[7])+', KAWIN = '+
                    quotedstr(splitter[8])+', KELAMIN = '+
                    quotedstr(splitter[9])+', KELAINAN = '+
                    quotedstr(splitter[10])+', ALAMAT = '+
                    quotedstr(splitter[11])+', RW = '+
                    quotedstr(splitter[12])+', RT = '+
                    quotedstr(splitter[13])+', DUSUN= '+
                    quotedstr(splitter[14])+', KET= '+
                    quotedstr(splitter[15])+', ID_TANGGAL= '+
                    quotedstr(splitter[16])+', TPS='+
                    quotedstr(gettps(splitter[17], listtpsinput,listtpsoutput,listtpsasal,desaproses))+', STATUS1= '+
                    quotedstr(splitter[18])+', STATUS2= '+
                    quotedstr(splitter[19])+', GANDA= '+
                    quotedstr(splitter[20])+', STATUS3= '+
                    quotedstr(splitter[21])+', STATUSP= '+
                    quotedstr(splitter[22])+', ALASANP= '+
                    quotedstr(splitter[23])+', CACAT= '+
                    quotedstr(splitter[24])+', PENDIDIKAN= '+
                    quotedstr(splitter[25])+', PEKERJAAN= '+
                    quotedstr(splitter[26])+', STATUSR= '+
                    quotedstr(splitter[27])+', WN= '+
                    quotedstr(splitter[28])+' '+updateque;
                    //showmessage(updateque);
                    with zquery2 do
                    begin
                      active:=false;
                      sql.clear;
                      sql.text:=updateque;
                      execsql;
                    end;
                  end;
                  //showmessage('ah euh');
                  splitter.Free;
                  Readln(zfile, text);
                  temp:='';
                  //showmessage('readln '+text);
                end
                else if (splitter.Count > 29) then begin
                  Readln(zfile, text);
                  splitter.Free;
                  temp:='';
                end else
                  Readln(zfile, text);
              end;

              //showmessage('resume '+tpsquery);
              {if(tpsquery<>'')then
              begin
                with zquery2 do
                begin
                  active:=false;
                  sql.clear;
                  sql.text:='insert into '+namatabel+' (NIK, NKK, NAMA, TEMPAT, TANGGAL, IBU, AGAMA, KAWIN, KELAMIN, KELAINAN, ALAMAT, RW, RT, DUSUN, KET, ID_TANGGAL, TPS, STATUS1, STATUS2, GANDA, STATUS3, STATUSP, ALASANP, CACAT, PENDIDIKAN, PEKERJAAN, STATUSR, WN) values '+tpsquery;
                  execsql;
                end;
              end; }


            end;
            Readln(zfile, text);
            //showmessage('readln '+text);
          end;

        end
        else if(text='[PETUGAS]')then
        begin
          batas:=0;
        //showmessage('hore');
          tpsquery:='';
          Readln(zfile, text);
          while(text<>tandajeda)do
          begin
            text:=StringReplace(text,'''',' ',[rfReplaceAll]);
            text:=StringReplace(text,'"',' ',[rfReplaceAll]);
            text:=StringReplace(text,'\',' ',[rfReplaceAll]);
            lolos := true;
            splitter:=tstringlist.Create;
            ExtractStrings(['$'], [], PChar(text), splitter);
            //menentukan lolos atau enggak
            querycek := 'SELECT ID_PETUGAS FROM petugas WHERE '
              +'POSISI = '+quotedStr(splitter[2])+' '
              +'AND KODE_KABKOT = '+quotedStr(splitter[3])+' '
              +'AND KODE_KECAMATAN = '+quotedStr(splitter[4])+' '
              +'AND KODE_DESA = '+quotedStr(splitter[5])+' '
              +'AND JABATAN = '+quotedStr(splitter[6]);

            //menentukan syarat
            if(splitter[6] = '1') then
              syarat:=1
            else if(splitter[2] = '4') then
              syarat:=2
            else
              syarat:=4;

            zquery2.Active:=false;
            zquery2.SQL.Clear;
            zquery2.SQL.Text:=querycek;
            zquery2.Active:=true;

            if (zquery2.RecordCount = syarat) then
              lolos:=false;

            tpsquery:='('+quotedstr(splitter[1])+', '+quotedstr(splitter[2])+','+quotedstr(splitter[3])+','+quotedstr(splitter[4])+','+quotedstr(splitter[5])+','+quotedstr(splitter[6])+')';

            if((tpsquery<>'') and lolos) then
            begin
              with zquery2 do
              begin
                active:=false;
                sql.clear;
                sql.text:='insert into petugas (NAMA, POSISI, KODE_KABKOT, KODE_KECAMATAN, KODE_DESA, JABATAN) values '+tpsquery;
                execsql;
              end;
            end;

            splitter.Free;
            Readln(zfile, text);
          end;
        end;
      end;
      CloseFile(zfile);
      //showmessage('selesai');
      //showmessage('insert into log_sinkronisasi (ASAL,USERNAME,TANGGAL) values ('+quotedstr(kode)+','+quotedstr(user)+','+quotedstr(tanggalbuat)+')');
      with zquery do
      begin
        active:=false;
        sql.clear;
        sql.text:='insert into log_sinkronisasi (ASAL,USERNAME,TANGGAL) values ('+quotedstr(kode)+','+quotedstr(user)+','+quotedstr(tanggalbuat)+')';
        execsql;
      end;
    end;
    end;
  lastDate:=text;
  result:=hasil;
end;

end.
